import mysql.connector
from config import DB_CONFIG


def get_db_connection():
    try:
        return mysql.connector.connect(**DB_CONFIG)
    except mysql.connector.Error as err:
        print(f"数据库连接错误: {err}")
        return None


def insert_message(content):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = "INSERT INTO messages (content) VALUES (%s)"
        cursor.execute(query, (content,))
        conn.commit()
    except Exception as e:
        print(f"{e}")
    finally:
        cursor.close()
        conn.close()


def update_message(message_id, content):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = "UPDATE messages SET content = %s WHERE id = %s"
        cursor.execute(query, (content, message_id))
        conn.commit()
    except Exception as e:
        print(f"{e}")
    finally:
        cursor.close()
        conn.close()


def delete_message(message_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = "DELETE FROM messages WHERE id = %s"
        cursor.execute(query, (message_id,))
        conn.commit()
    except Exception as e:
        print(f"{e}")
    finally:
        cursor.close()
        conn.close()


def get_messages(page, per_page):
    conn = get_db_connection()
    cursor = conn.cursor()
    offset = (page - 1) * per_page
    try:
        query = "SELECT id, content FROM messages LIMIT %s OFFSET %s"
        cursor.execute(query, (per_page, offset))
        messages = cursor.fetchall()
        return messages
    except Exception as e:
        print(f"{e}")
    finally:
        cursor.close()
        conn.close()


def get_message_by_id(message_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = "SELECT id, content FROM messages WHERE id = %s"
        cursor.execute(query, (message_id,))
        message = cursor.fetchone()
        return message
    except Exception as e:
        print(f"{e}")
    finally:
        cursor.close()
        conn.close()


def get_total_messages():
    """获取留言总数，用于计算分页"""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = "SELECT COUNT(*) FROM messages"
        cursor.execute(query)
        count = cursor.fetchone()[0]
        return count
    except Exception as e:
        print(f"{e}")
        return 0
    finally:
        cursor.close()
        conn.close()
